﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Xml.Linq;
using System.Configuration;
using System.Xml;

namespace ClassLibrary
{
    /// <summary>
    /// 操作Sql Server的基础类，源于Microsoft SqlHelper类
    /// 减去了事务和类型化参数等,增加默认连接对象
    /// </summary>
    public sealed class Sqlsrv
    {
        #region 静态构造函数 内部成员和方法
        /// <summary>
        /// 静态构造函数
        /// </summary>
        static Sqlsrv() 
        {
            _connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        }
        /// <summary>
        /// 连接字符串
        /// </summary>
        private static String _connectionString;
        /// <summary>
        /// 获取或设置连接字符串
        /// </summary>
        public static String ConnectionString
        {
            get
            {
                if (String.IsNullOrEmpty(_connectionString))
                {
                    //_connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                    throw new Exception("配置文件连接对象\"ConnectionString\"有误，或未进行相应配置！");
                }
                return _connectionString;
            }
            set
            {
                if (!String.IsNullOrEmpty(value))
                    _connectionString = value;
            }
        }

        /// <summary>
        /// 数据库连接对象
        /// </summary>
        private static SqlConnection _connection;
        /// <summary>
        /// 获取数据库连接对象
        /// 直接用公开的SqlConnection,可能会造成连接排队
        /// 最好先克隆该成员或者调用GetConnInstance()
        /// </summary>
        public static SqlConnection Connection
        {
            get
            {
                if (_connection == null)
                {
                    _connection = new SqlConnection(ConnectionString);
                    _connection.Open();
                }
                else if (_connection.State == System.Data.ConnectionState.Closed)
                {
                    _connection.Open();
                }
                else if (_connection.State == System.Data.ConnectionState.Broken)
                {
                    _connection.Close();
                    _connection.Open();
                }
                return _connection;
            }
            private set
            {
                _connection = value;
            }
        }

        /// <summary>
        /// 获取默认连接对象(克隆)
        /// </summary>
        /// <returns></returns>
        public static SqlConnection GetConnInstance()
        {
            return (SqlConnection)((ICloneable)Connection).Clone();
        }

        /// <summary>
        /// [重载]获取自定义连接对象
        /// </summary>
        /// <param name="Connstring">连接字符串</param>
        /// <returns></returns>
        public static SqlConnection GetConnInstance(string Connstring)
        {
            return new SqlConnection(Connstring);
        }

        /// <summary>
        /// 将SqlParameter参数数组(参数值)分配给SqlCommand命令.
        /// 这个方法将给任何一个参数分配DBNull.Value;
        /// 该操作将阻止默认值的使用.
        /// </summary>
        /// <param name="command">命令名</param>
        /// <param name="commandParameters">SqlParameters数组</param>
        private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandParameters != null && commandParameters.Length>0)
            {
                foreach (SqlParameter p in commandParameters)
                {
                    if (p != null)
                    {
                        // 检查未分配值的输出参数,将其分配以DBNull.Value.
                        if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
                            (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }
                }
            }
        }

        /// <summary>
        /// 将DataRow类型的列值分配到SqlParameter参数数组.
        /// </summary>
        /// <param name="commandParameters">要分配值的SqlParameter参数数组</param>
        /// <param name="dataRow">将要分配给存储过程参数的DataRow</param>
        private static void AssignParameterValues(SqlParameter[] commandParameters, DataRow dataRow)
        {
            if ((commandParameters == null) || (dataRow == null))
            {
                return;
            }
            int i = 0;
            // 设置参数值
            foreach (SqlParameter commandParameter in commandParameters)
            {
                // 创建参数名称,如果不存在,只抛出一个异常.
                if (commandParameter.ParameterName == null ||
                    commandParameter.ParameterName.Length <= 1)
                    throw new Exception(
                        string.Format("请提供参数{0}一个有效的名称{1}.", i, commandParameter.ParameterName));
                // 从dataRow的表中获取为参数数组中数组名称的列的索引.
                // 如果存在和参数名称相同的列,则将列值赋给当前名称的参数.
                if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
                    commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
                i++;
            }
        }

        /// <summary>
        /// 将一个对象数组分配给SqlParameter参数数组.
        /// </summary>
        /// <param name="commandParameters">要分配值的SqlParameter参数数组</param>
        /// <param name="parameterValues">将要分配给存储过程参数的对象数组</param>
        private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
        {
            if ((commandParameters == null) || (parameterValues == null))
            {
                return;
            }
            // 确保对象数组个数与参数个数匹配,如果不匹配,抛出一个异常.
            if (commandParameters.Length != parameterValues.Length)
            {
                throw new ArgumentException("参数值个数与参数不匹配.");
            }
            // 给参数赋值
            for (int i = 0, j = commandParameters.Length; i < j; i++)
            {
                // If the current array value derives from IDbDataParameter, then assign its Value property
                if (parameterValues[i] is IDbDataParameter)
                {
                    IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
                    if (paramInstance.Value == null)
                    {
                        commandParameters[i].Value = DBNull.Value;
                    }
                    else
                    {
                        commandParameters[i].Value = paramInstance.Value;
                    }
                }
                else if (parameterValues[i] == null)
                {
                    commandParameters[i].Value = DBNull.Value;
                }
                else
                {
                    commandParameters[i].Value = parameterValues[i];
                }
            }
        }

        /// <summary>
        /// 探索运行时的存储过程,返回SqlParameter参数数组.
        /// 初始化参数值为 DBNull.Value.
        /// </summary>
        /// <param name="connection">一个有效的数据库连接</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="includeReturnParameter">是否包含返回值参数</param>
        /// <returns>返回SqlParameter参数数组</returns>
        private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnParameter=false)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (String.IsNullOrEmpty(spName)) throw new ArgumentNullException("spName");
            using (SqlCommand cmd = new SqlCommand(spName, connection))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                connection.Open();
                // 检索cmd指定的存储过程的参数信息,并填充到cmd的Parameters参数集中.
                SqlCommandBuilder.DeriveParameters(cmd);
                connection.Close();
                // 如果不包含返回值参数,将参数集中的每一个参数删除.
                if (!includeReturnParameter)
                {
                    cmd.Parameters.RemoveAt(0);
                }

                // 创建参数数组
                SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];
                // 将cmd的Parameters参数集复制到discoveredParameters数组.
                cmd.Parameters.CopyTo(discoveredParameters, 0);
                // 初始化参数值为 DBNull.Value.
                foreach (SqlParameter discoveredParameter in discoveredParameters)
                {
                    discoveredParameter.Value = DBNull.Value;
                }
                return discoveredParameters;
            }
        }
        #endregion

        #region ExecuteNonQuery 返回影响的行数
        /// <summary>
        /// 执行无参Transact-SQL语句
        /// 例如：Insert Update Delete语句
        /// </summary>
        /// <param name="cmdText">SQL文本命令</param>
        /// <returns>返回命令影响的行数</returns>
        public static int ExecuteNonQuery(string cmdText)
        {
            return ExecuteNonQuery(cmdText,null);
        }

        /// <summary>
        /// [重载]执行有参Transact-SQL语句
        /// 例如：Insert Update Delete语句
        /// </summary>
        /// <param name="cmdText">SQL文本命令</param>
        /// <param name="values">SqlParameter参数数组</param>
        /// <returns>返回命令影响的行数</returns>
        public static int ExecuteNonQuery(string cmdText, params SqlParameter[] values)
        {
            return ExecuteNonQuery(CommandType.Text, cmdText, values);
        }

        /// <summary>
        /// 执行指定连接字符串的存储过程,将对象数组的值赋给存储过程参数,
        /// 此方法需要在参数缓存方法中探索参数并生成参数.
        /// </summary>
        /// <remarks>
        /// 这个方法没有提供访问输出参数和返回值.
        /// 示例:  
        ///  int result = ExecuteNonQuery("PublishOrders", 24, 36);
        /// </remarks>
        /// <param name="spName">存储过程名</param>
        /// <param name="values">分配到存储过程输入参数的对象数组</param>
        /// <returns>返回受影响的行数</returns>
        public static int ExecuteNonQuery(string spName, params object[] values)
        {
            if (String.IsNullOrEmpty(spName))
                throw new ArgumentNullException("spName");
            // 如果存在参数值
            if (values != null && values.Length > 0)
            {
                using (SqlConnection conn = GetConnInstance())
                {
                    // 从探索存储过程参数并分配给存储过程参数数组.
                    SqlParameter[] commandParameters = DiscoverSpParameterSet(conn, spName);
                    // 给存储过程参数赋值
                    AssignParameterValues(commandParameters, values);
                    return ExecuteNonQuery(CommandType.StoredProcedure, spName, commandParameters);
                }
            }
            else
            {
                // 没有参数情况下
                return ExecuteNonQuery(CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行Transact-SQL语句或存储过程，并返回受影响的行数。
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  int result = ExecuteNonQuery(CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="commType">命令类型(存储过程,命令文本或其它.)</param>
        /// <param name="cmdText">存储过程名称或T-SQL语句</param>
        /// <param name="values">SqlParamter参数数组</param>
        /// <returns>返回影响的行数</returns>
        public static int ExecuteNonQuery(CommandType commType,string cmdText, params SqlParameter[] values)
        {
            try
            {
                using (SqlConnection conn = GetConnInstance())
                {
                    SqlCommand cmd = new SqlCommand(cmdText)
                    {
                        Connection = conn,
                        CommandType = commType
                    };
                    AttachParameters(cmd, values);
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 执行多条Transact-SQL语句
        /// 例如多条Insert或Update命令
        /// </summary>
        /// <param name="cmdTexts">SQL文本命令集</param>
        /// <returns>返回执行结果(True or False)</returns>
        public static bool ExcuteNonQuery(IList<string> cmdTexts)
        {
            Boolean flag = false;
            try
            {
                using (SqlConnection conn = GetConnInstance())
                {
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    foreach (string cmdText in cmdTexts)
                    {
                        comm.CommandText = cmdText;
                        comm.ExecuteNonQuery();
                    }
                    comm.Dispose();
                }
                flag = true;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return flag;
        }
        #endregion

        #region ExecuteScalar 返回结果集中的第一行第一列
        /// <summary>
        ///  执行聚合函数或者取第一行 第一列(无参)
        /// </summary>
        /// <param name="cmdText">T-SQL语句</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(string cmdText)
        {
            return ExecuteScalar(cmdText, null);
        }

        /// <summary>
        /// [重载]执行聚合函数或者取第一行 第一列(有参)
        /// </summary>
        /// <param name="cmdText">T-SQL语句</param>
        /// <param name="values">分配给命令的SqlParamter参数数组</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(string cmdText, params SqlParameter[] values)
        {
            return ExecuteScalar(CommandType.Text, cmdText, values);
        }

        /// <summary>
        /// 执行存储过程命令,指定参数值,返回结果集中的第一行第一列.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输出参数和返回值参数.
        /// 示例:  
        ///  int orderCount = (int)ExecuteScalar("GetOrderCount", "GetOrderCount", 24, 36);
        /// </remarks>
        /// <param name="spName">存储过程名称</param>
        /// <param name="values">分配给存储过程输入参数的对象数组</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(string spName, params object[] values)
        {
            if (String.IsNullOrEmpty(spName)) throw new ArgumentNullException("spName");

            // 如果有参数值
            if ((values != null) && (values.Length > 0))
            {
                using (SqlConnection conn = GetConnInstance())
                {
                    //从数据库中检索参数信息.
                    SqlParameter[] commandParameters = DiscoverSpParameterSet(conn, spName);
                    // 给存储过程参数赋值
                    AssignParameterValues(commandParameters, values);
                    // 调用重载方法
                    return ExecuteScalar(spName, CommandType.StoredProcedure, commandParameters);
                }
            }
            else
            {
                // 没有参数值
                return ExecuteScalar(spName, CommandType.StoredProcedure);
            }
        }

        /// <summary>
        /// 执行默认数据库连接对象的命令,指定参数,返回结果集中的第一行第一列.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  int orderCount = (int)ExecuteScalar(CommandType.StoredProcedure,"GetOrderCount",new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="commType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="cmdText">存储过程名称或T-SQL语句</param>
        /// <param name="values">分配给命令的SqlParamter参数数组</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(CommandType commType,string cmdText, params SqlParameter[] values)
        {
            try
            {
                using (SqlConnection conn = GetConnInstance())
                {
                    // 创建SqlCommand命令,并进行预处理
                    SqlCommand cmd = new SqlCommand(cmdText)
                    {
                        Connection = conn,
                        CommandType = commType
                    };
                    // 分配命令参数
                    AttachParameters(cmd, values);
                    // 执行SqlCommand命令,并返回结果.
                    return cmd.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion ExecuteScalar

        #region ExecuteDataset 结果集
        /// <summary>
        /// 执行T-SQL语句,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 示例: 
        ///  DataSet ds = ExecuteDataset("select * from Users");
        /// </remarks>
        /// <param name="cmdText">T-SQL语句</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataset(string cmdText)
        {
            return ExecuteDataset(cmdText, null);
        }

        /// <summary>
        /// [重载]执行指定T-SQL语句带参数,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 示例: 
        ///  DataSet ds = ExecuteDataset("select * from Procdut", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="cmdText">T-SQL语句</param>
        /// <param name="values">SqlParamters参数数组</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataset(string cmdText, params SqlParameter[] values)
        {
            // 调用重载方法.
            return ExecuteDataset(CommandType.Text, cmdText, values);

        }

        /// <summary>
        /// 执行存储过程,直接提供参数值,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输出参数和返回值.
        /// 示例: 
        ///  DataSet ds = ExecuteDataset("GetOrders", 24, 36);
        /// </remarks>
        /// <param name="spName">存储过程名</param>
        /// <param name="values">分配给存储过程输入参数的对象数组</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataset(string spName, params object[] values)
        {
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

            if ((values != null) && (values.Length > 0))
            {
                using (SqlConnection conn = GetConnInstance())
                {
                    // 从数据库中检索存储过程参数
                    SqlParameter[] commandParameters = DiscoverSpParameterSet(conn, spName);
                    // 给存储过程参数分配值
                    AssignParameterValues(commandParameters, values);
                    return ExecuteDataset(CommandType.StoredProcedure, spName, commandParameters);
                }
            }
            else
            {
                return ExecuteDataset(CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行指定T-Sql命令或存储过程，指定参数,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  DataSet ds = ExecuteDataset(CommandType.StoredProcedure,"GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="commType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="cmdText">存储过程名或T-SQL语句</param>
        /// <param name="values">SqlParamter参数数组</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataset(CommandType commType,string cmdText, params SqlParameter[] values)
        {
            try
            {
                // 创建SqlCommand命令,并进行预处理
                SqlCommand cmd = new SqlCommand(cmdText) { CommandType = commType };
                // 分配命令参数
                AttachParameters(cmd, values);
                // 创建SqlDataAdapter和DataSet.
                using (SqlDataAdapter da = new SqlDataAdapter(cmdText, ConnectionString))
                {
                    DataSet ds = new DataSet();
                    // 填充DataSet.
                    da.Fill(ds);
                    //释放SqlCommand资源
                    cmd.Dispose();
                    return ds;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// [重载]执行指定数据库连接对象的命令,指定参数值,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输入参数和返回值.
        /// 示例:  
        ///  DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="values">分配给存储过程输入参数的对象数组</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] values)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
            if ((values != null) && (values.Length > 0))
            {
                // 比缓存中加载存储过程参数
                SqlParameter[] commandParameters = DiscoverSpParameterSet(connection,spName);
                // 给存储过程参数分配值
                AssignParameterValues(commandParameters, values);
                return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return ExecuteDataset(connection, CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// [重载]执行指定数据库连接对象的命令,指定存储过程参数,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="commType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="cmdText">存储过程名或T-SQL语句</param>
        /// <param name="values">SqlParamter参数数组</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataset(SqlConnection connection, CommandType commType, string cmdText, params SqlParameter[] values)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            try
            {
                // 预处理
                SqlCommand cmd = new SqlCommand(cmdText, connection);
                cmd.CommandType = commType;
                AttachParameters(cmd, values);

                // 创建SqlDataAdapter和DataSet.
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    // 填充DataSet.
                    da.Fill(ds);
                    cmd.Parameters.Clear();
                    return ds;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion ExecuteDataset数据集命令结束

        #region ExecuteReader 数据阅读器
        /// <summary>
        /// 执行T-SQL语句,返回数据阅读器.
        /// </summary>
        /// <param name="cmdText">存储过程名或T-SQL语句</param>
        /// <returns>返回包含结果集的SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string cmdText)
        {
            return ExecuteReader(cmdText, null);
        }

        /// <summary>
        /// [重载]执行指定T-SQL语句附参数，返回数据阅读器.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  SqlDataReader dr = ExecuteReader("select * from Procdut where ID=@prodid", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="cmdText">T-SQL语句</param>
        /// <param name="values">SqlParamter参数数组(new SqlParameter("@prodid", 24))</param>
        /// <returns>返回包含结果集的SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] values)
        {
            return ExecuteReader(CommandType.Text, cmdText, values);

        }

        /// <summary>
        /// 执行默认数据库连接字符串的数据阅读器,指定参数值.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输出参数和返回值参数.
        /// 示例:  
        ///  SqlDataReader dr = ExecuteReader("GetOrders", 24, 36);
        /// </remarks>
        /// <param name="spName">存储过程名</param>
        /// <param name="values">分配给存储过程输入参数的对象数组</param>
        /// <returns>返回包含结果集的SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string spName, params object[] values)
        {
            if (String.IsNullOrEmpty(spName)) throw new ArgumentNullException("spName");

            if ((values != null) && (values.Length > 0))
            {
                using (SqlConnection conn = GetConnInstance())
                {
                    SqlParameter[] commandParameters = DiscoverSpParameterSet(conn, spName);
                    AssignParameterValues(commandParameters, values);
                    return ExecuteReader(CommandType.StoredProcedure, spName, commandParameters);
                }
            }
            else
            {
                return ExecuteReader(CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行默认的数据库连接对象的数据阅读器.
        /// 如果关闭关联的 DataReader 对象，则关联的Connection对象也将关闭
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  SqlDataReader dr = ExecuteReader(CommandType.StoredProcedure, "GetOrders",new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="commType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="cmdText">存储过程名或T-SQL语句</param>
        /// <param name="values">SqlParameters参数数组,如果没有参数则为'null'</param>
        /// <returns>返回包含结果集的SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(CommandType commType, string cmdText, params SqlParameter[] values)
        {
            // 创建连接对象
            SqlConnection conn = null;
            try
            {
                conn = GetConnInstance();
                // 创建命令
                SqlCommand cmd = new SqlCommand(cmdText, conn);
                cmd.CommandType = commType;
                AttachParameters(cmd,values);
                // 创建数据阅读器
                SqlDataReader dataReader;
                dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                // 清除参数,以便再次使用..
                bool canClear = true;
                foreach (SqlParameter commandParameter in cmd.Parameters)
                {
                    if (commandParameter.Direction != ParameterDirection.Input)
                        canClear = false;
                }
                if (canClear)
                {
                    cmd.Parameters.Clear();
                }
                return dataReader;
            }
            catch (SqlException ex)
            {
                if (conn != null)
                    conn.Dispose();
                throw ex;
            }
            catch (Exception ex)
            {
                if (conn != null)
                    conn.Dispose();
                throw ex;
            }
        }

        /// <summary>
        /// [重载]执行指定的数据库连接对象的数据阅读器.
        /// 如果关闭关联的 DataReader 对象，则关联的Connection对象也将关闭
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  SqlDataReader dr = ExecuteReader(conn,CommandType.StoredProcedure, "GetOrders",new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="commType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="cmdText">存储过程名或T-SQL语句</param>
        /// <param name="values">SqlParameters参数数组,如果没有参数则为'null'</param>
        /// <returns>返回包含结果集的SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commType, string cmdText, params SqlParameter[] values)
        {
            try
            {
                if (connection.State != ConnectionState.Open) connection.Open(); //打开连接
                // 创建命令
                SqlCommand cmd = new SqlCommand(cmdText, connection);
                cmd.CommandType = commType;
                AttachParameters(cmd, values);
                // 创建返回数据阅读器
                return cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// [重载]执行指定数据库连接对象的数据阅读器,指定参数值.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输出参数和返回值参数.
        /// 示例:  
        ///  SqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="spName">T存储过程名</param>
        /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>
        /// <returns>返回包含结果集的SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(SqlConnection connection,string spName, params object[] values)
        {
            if (String.IsNullOrEmpty(spName)) throw new ArgumentNullException("spName");

            if ((values != null) && (values.Length > 0))
            {
                SqlParameter[] commandParameters = DiscoverSpParameterSet(connection, spName);
                    AssignParameterValues(commandParameters, values);
                    return ExecuteReader(connection,CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                return ExecuteReader(connection,CommandType.StoredProcedure, spName);
            }
        }

        #endregion ExecuteReader数据阅读器

        #region ExecuteXmlReader XML阅读器
        /// <summary>
        /// 执行默认数据库连接对象的SqlCommand命令,并产生一个XmlReader对象做为结果集返回.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  XmlReader r = ExecuteXmlReader("select * from product for xml auto");
        /// </remarks>
        /// <param name="cmdText">T-SQL语句 using "FOR XML AUTO"</param>
        /// <returns>返回XmlReader结果集对象.</returns>
        public static XmlReader ExecuteXmlReader(string cmdText)
        {
            // 执行参数为空的方法
            return ExecuteXmlReader(cmdText, null);
        }

        /// <summary>
        /// [重载]执行默认数据库连接对象的SqlCommand命令,并产生一个XmlReader对象做为结果集返回.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders");
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="cmdText">T-SQL语句 using "FOR XML AUTO"</param>
        /// <returns>返回XmlReader结果集对象.</returns>
        public static XmlReader ExecuteXmlReader(string cmdText,params SqlParameter[] values)
        {
            // 执行参数不为空的方法
            return ExecuteXmlReader(CommandType.Text, cmdText,values);
        }

        /// <summary>
        /// 执行默认数据库连接对象的SqlCommand命令,并产生一个XmlReader对象做为结果集返回,指定参数值.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输出参数和返回值参数.
        /// 示例:  
        ///  XmlReader r = ExecuteXmlReader("GetOrders", 24, 36);
        /// </remarks>
        /// <param name="spName">存储过程名称 using "FOR XML AUTO"</param>
        /// <param name="values">分配给存储过程输入参数的对象数组</param>
        /// <returns>返回XmlReader结果集对象.</returns>
        public static XmlReader ExecuteXmlReader(string spName, params object[] values)
        {
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
            // 如果有参数值
            if ((values != null) && (values.Length > 0))
            {
                using (SqlConnection conn = GetConnInstance())
                {
                    // 从数据库中检索参数信息.
                    SqlParameter[] commandParameters = DiscoverSpParameterSet(conn, spName);
                    // 给存储过程参数赋值
                    AssignParameterValues(commandParameters, values);
                    // 调用重载方法
                    return ExecuteXmlReader(CommandType.StoredProcedure, spName, commandParameters);
                }
            }
            else
            {
                // 没有参数值
                return ExecuteXmlReader(CommandType.StoredProcedure, spName);
            }
        }

        /// <summary>
        /// 执行默认数据库连接对象的SqlCommand命令,并产生一个XmlReader对象做为结果集返回,指定参数.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  XmlReader r = ExecuteXmlReader(CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="commType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="cmdText">存储过程名称或T-SQL语句 using "FOR XML AUTO"</param>
        /// <param name="values">分配给命令的SqlParamter参数数组</param>
        /// <returns>返回XmlReader结果集对象.</returns>
        public static XmlReader ExecuteXmlReader(CommandType commType, string cmdText, params SqlParameter[] values)
        {
            // 创建连接对象
            SqlConnection conn = null;
            try
            {
                conn = GetConnInstance();
                // 创建SqlCommand命令,并进行预处理
                SqlCommand cmd = new SqlCommand(cmdText, conn);
                cmd.CommandType = commType;
                //分配命令参数
                AttachParameters(cmd, values);
                // 执行命令
                XmlReader retval = cmd.ExecuteXmlReader();

                // 清除参数,以便再次使用.
                cmd.Parameters.Clear();
                return retval;
            }
            catch (Exception ex)
            {
                if (conn != null)
                    conn.Dispose();
                throw ex;
            }
        }

        /// <summary>
        /// [重载]执行指定数据库连接对象的SqlCommand命令,并产生一个XmlReader对象做为结果集返回,指定参数.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="commType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="cmdText">存储过程名称或T-SQL语句 using "FOR XML AUTO"</param>
        /// <param name="values">分配给命令的SqlParamter参数数组</param>
        /// <returns>返回XmlReader结果集对象.</returns>
        public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commType, string cmdText, params SqlParameter[] values)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            try
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                // 创建SqlCommand命令,并进行预处理
                SqlCommand cmd = new SqlCommand(cmdText, connection);
                cmd.CommandType = commType;
                //分配命令参数
                AttachParameters(cmd, values);
                // 执行命令
                XmlReader retval = cmd.ExecuteXmlReader();

                // 清除参数,以便再次使用.
                cmd.Parameters.Clear();
                return retval;
            }
            catch (SqlException ex)
            {
                connection.Close();
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// [重载]执行指定数据库连接对象的SqlCommand命令,并产生一个XmlReader对象做为结果集返回,指定参数值.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输出参数和返回值参数.
        /// 示例:  
        ///  XmlReader r = ExecuteXmlReader(conn, "GetOrders", 24, 36);
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="spName">存储过程名称 using "FOR XML AUTO"</param>
        /// <param name="values">分配给存储过程输入参数的对象数组</param>
        /// <returns>返回XmlReader结果集对象.</returns>
        public static XmlReader ExecuteXmlReader(SqlConnection connection, string spName, params object[] values)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
            // 如果有参数值
            if ((values != null) && (values.Length > 0))
            {
                // 从数据库中检索参数信息.
                SqlParameter[] commandParameters = DiscoverSpParameterSet(connection, spName);
                // 给存储过程参数赋值
                AssignParameterValues(commandParameters, values);
                // 调用重载方法
                return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
            }
            else
            {
                // 没有参数值
                return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
            }
        }
        #endregion ExecuteXmlReader 阅读器结束

        #region FillDataset 填充数据集
        /// <summary>
        /// 执行默认数据库对象的命令,映射数据表并填充数据集.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  FillDataset("select * from orders", ds, new string[] {"orders"});
        /// </remarks>
        /// <param name="commandText">存储过程名称或T-SQL语句</param>
        /// <param name="dataSet">要填充结果集的DataSet实例</param>
        /// <param name="tableNames">表映射的数据表数组
        /// 用户定义的表名 (可有是实际的表名.)</param>
        public static void FillDataset(string commandText, DataSet dataSet, string[] tableNames)
        {
           FillDataset(commandText, dataSet, tableNames,null);
        }

        /// <summary>
        /// [重载]执行默认数据库对象的命令,映射数据表并填充数据集.指定命令参数.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  FillDataset("select * from orders", ds, new string[] {"orders"},values);
        /// </remarks>
        /// <param name="cmdText">T-SQL语句</param>
        /// <param name="values">分配给命令的SqlParamter参数数组</param>
        /// <param name="dataSet">要填充结果集的DataSet实例</param>
        /// <param name="tableNames">表映射的数据表数组
        /// 用户定义的表名 (可有是实际的表名.)
        /// </param>
        public static void FillDataset(string cmdText, DataSet dataSet, string[] tableNames, params SqlParameter[] values)
        {
            FillDataset(CommandType.Text, cmdText, dataSet, tableNames, values);
        }

        /// <summary>
        /// 执行默认数据库连接对象的命令,映射数据表并填充数据集,指定存储过程参数值.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输出参数和返回值参数.
        /// 示例:  
        ///  FillDataset("GetOrders", ds, new string[] {"orders"}, 24);
        /// </remarks>
        /// <param name="spName">存储过程名称</param>
        /// <param name="dataSet">要填充结果集的DataSet实例</param>
        /// <param name="tableNames">表映射的数据表数组
        /// 用户定义的表名 (可有是实际的表名.)
        /// </param>    
        /// <param name="values">分配给存储过程输入参数的对象数组</param>
        public static void FillDataset(string spName,DataSet dataSet, string[] tableNames,params object[] values)
        {
            // 创建并打开数据库连接对象,操作完成释放对象.
            using (SqlConnection connection = GetConnInstance())
            {
                // 调用指定数据库连接字符串重载方法.
                FillDataset(connection, spName, dataSet, tableNames, values);
            }
        }

        /// <summary>
        /// 执行默认数据库连接对象的命令,映射数据表并填充数据集.指定命令参数.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  FillDataset(CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="commType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="cmdText">存储过程名称或T-SQL语句</param>
        /// <param name="values">分配给命令的SqlParamter参数数组</param>
        /// <param name="dataSet">要填充结果集的DataSet实例</param>
        /// <param name="tableNames">表映射的数据表数组
        /// 用户定义的表名 (可有是实际的表名.)
        /// </param>
        public static void FillDataset(CommandType commType,string cmdText, 
            DataSet dataSet, string[] tableNames,params SqlParameter[] values)
        {
            if (dataSet == null) throw new ArgumentNullException("dataSet");
            // 创建并打开数据库连接对象,操作完成释放对象.
            using (SqlConnection connection = GetConnInstance())
            {
                // 调用指定数据库连接字符串重载方法.
                FillDataset(connection, commType, cmdText, dataSet, tableNames, values);
            }
        }

        /// <summary>
        /// [重载]执行指定数据库连接对象的命令,映射数据表并填充数据集,指定存储过程参数值.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输出参数和返回值参数.
        /// 示例:  
        ///  FillDataset(conn, "GetOrders", ds, new string[] {"orders"}, 24, 36);
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="dataSet">要填充结果集的DataSet实例</param>
        /// <param name="tableNames">表映射的数据表数组
        /// 用户定义的表名 (可有是实际的表名.)
        /// </param>
        /// <param name="values">分配给存储过程输入参数的对象数组</param>
        public static void FillDataset(SqlConnection connection, string spName,
            DataSet dataSet, string[] tableNames, params object[] values)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (dataSet == null) throw new ArgumentNullException("dataSet");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
            // 如果有参数值
            if ((values != null) && (values.Length > 0))
            {
                // 从数据库中检索参数信息. 
                SqlParameter[] commandParameters = DiscoverSpParameterSet(connection, spName);
                // 给存储过程参数赋值
                AssignParameterValues(commandParameters, values);
                // 调用重载方法
                FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
            }
            else
            {
                // 没有参数值
                FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames);
            }
        }

        /// <summary>
        /// [重载]执行指定数据库连接对象/事务的命令,映射数据表并填充数据集,DataSet/TableNames/SqlParameters.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="commandText">存储过程名称或T-SQL语句</param>
        /// <param name="dataSet">要填充结果集的DataSet实例</param>
        /// <param name="tableNames">表映射的数据表数组
        /// 用户定义的表名 (可有是实际的表名.)
        /// </param>
        /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param>
        private static void FillDataset(SqlConnection connection, CommandType commType,string cmdText, 
            DataSet dataSet, string[] tableNames,params SqlParameter[] values)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (dataSet == null) throw new ArgumentNullException("dataSet");
            try
            {
                //if (connection.State != ConnectionState.Open) connection.Open();
                // 创建SqlCommand命令,并进行预处理
                SqlCommand command = new SqlCommand(cmdText, connection);
                command.CommandType = commType;
                AttachParameters(command, values);

                // 执行命令
                using (SqlDataAdapter da = new SqlDataAdapter(command))
                {
                    // 追加表映射
                    if (tableNames != null && tableNames.Length > 0)
                    {
                        string tableName = "Table";
                        for (int index = 0; index < tableNames.Length; index++)
                        {
                            if (tableNames[index] == null || tableNames[index].Length == 0) throw new ArgumentException("The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames");
                            da.TableMappings.Add(tableName, tableNames[index]);
                            tableName += (index + 1).ToString();
                        }
                    }

                    // 填充数据集使用默认表名称
                    da.Fill(dataSet);
                    // 清除参数,以便再次使用.
                    command.Parameters.Clear();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
           
        }
        #endregion

        #region UpdateDataset 更新数据集
        /// <summary>
        /// 执行数据集更新到数据库,指定inserted, updated, or deleted命令.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
        /// </remarks>
        /// <param name="insertCommand">[追加记录]一个有效的T-SQL语句或存储过程</param>
        /// <param name="deleteCommand">[删除记录]一个有效的T-SQL语句或存储过程</param>
        /// <param name="updateCommand">[更新记录]一个有效的T-SQL语句或存储过程</param>
        /// <param name="dataSet">要更新到数据库的DataSet</param>
        /// <param name="tableName">要更新到数据库的DataTable</param>
        public static void UpdateDataset(SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataSet dataSet, string tableName)
        {
            if (insertCommand == null) throw new ArgumentNullException("insertCommand");
            if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
            if (updateCommand == null) throw new ArgumentNullException("updateCommand");
            if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");
            // 创建SqlDataAdapter,当操作完成后释放.
            using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
            {
                // 设置数据适配器命令
                dataAdapter.UpdateCommand = updateCommand;
                dataAdapter.InsertCommand = insertCommand;
                dataAdapter.DeleteCommand = deleteCommand;
                // 更新数据集改变到数据库
                dataAdapter.Update(dataSet, tableName);
                // 提交所有改变到数据集.
                dataSet.AcceptChanges();
            }
        }
        #endregion

        #region CreateCommand 创建SqlCommand命令
        /// <summary>
        /// 创建SqlCommand命令,指定数据库连接对象,存储过程名和参数.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  SqlCommand command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName");
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="sourceColumns">源表的列名称数组</param>
        /// <returns>返回SqlCommand命令</returns>
        public static SqlCommand CreateCommand(SqlConnection connection, string spName, params string[] sourceColumns)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
            // 创建命令
            SqlCommand cmd = new SqlCommand(spName, connection);
            cmd.CommandType = CommandType.StoredProcedure;
            // 如果有参数值
            if ((sourceColumns != null) && (sourceColumns.Length > 0))
            {
                // 从数据库中检索参数信息
                SqlParameter[] commandParameters = DiscoverSpParameterSet(connection, spName);
                // 将源表的列到映射到DataSet命令中.
                for (int index = 0; index < sourceColumns.Length; index++)
                    commandParameters[index].SourceColumn = sourceColumns[index];
                // Attach the discovered parameters to the SqlCommand object
                AttachParameters(cmd, commandParameters);
            }
            return cmd;
        }
        #endregion
    }
}
